package weiy.top.core.sqlparaser;

import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.statement.values.ValuesStatement;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;
import org.springframework.util.DigestUtils;
import weiy.top.core.sqlparaser.schema.ColumnVo;
import weiy.top.core.sqlparaser.schema.TableVo;

import java.util.*;

@Component
public class AutoFormSelectVisitor implements SelectVisitor {


    /**
     * 获取sql语句中使用到的所有表名
     * 别名：真实字段名
     */
    private final Map<String, String> tableMappingMap = new HashMap<>();
    private final List<TableVo> tableVoList = new ArrayList<>();

    /**
     * 获取所有字段 （返回结果集用到的字段，不包含子查询用到的字段）
     */
    private final Map<String, Set<String>> map = new HashMap<>();

    /**
     * 获取查询的层级关系 (只表示子查询)
     * 比如：返回结果集的层级为顶级“0级”，下一个子查询的为“1级”， 子查询内的子查询为“2级别”。以此类推...
     */
    private final List<Map<String, String>> tree = new ArrayList<>();

    /**
     * 是否是多表联查
     */
    private boolean isJoin;

    /**
     * 表名，只有不是多表联查的时候，有数据
     */
    private String masterTableName;

    /**
     * 表别名，只有不是多表联查的时候，有数据
     */
    private String tableAliasName;

    /**
     * 获取所有字段 （返回结果集用到的字段，不包含子查询用到的字段）
     */
    private List<ColumnVo> columnVoList = new ArrayList<>();

    /*
        [
            {
               alias: "字段别名",
               realName: "字段真名",
               tempName: "字段临时名称"
            },
            ...
        ]
     */
    private final List<Map<String, String>> columnList = new ArrayList<>();

    private boolean state = true;


    @Override
    public void visit(PlainSelect pSelect) {
        FromItem fromItem = pSelect.getFromItem();
        parseSelect(pSelect, fromItem);
    }

    /**
     * @param pSelect
     * @param fromItem
     */
    private void parseSelect(PlainSelect pSelect, FromItem fromItem) {
        // 将查询字段按 字段名：别名 封装到map中
        List<SelectItem> selectColumn = pSelect.getSelectItems();

        for (SelectItem selectItem : selectColumn) {
            if (selectItem instanceof SelectExpressionItem) {
                SelectExpressionItem expressionItem = (SelectExpressionItem) selectItem;
                String aliasName = ""; // 字段别名
                if (expressionItem.getAlias() == null) { // 没有别名

                } else {   // 有别名
                    aliasName = expressionItem.getAlias().getName();
                }
                Expression expression = expressionItem.getExpression();
                analysisFunction(expression, aliasName);
            } else if (selectItem instanceof AllColumns) {
                String realName = selectItem.toString();

                ColumnVo columnVo = new ColumnVo();
                columnVo.setRealName(realName);
                columnVoList.add(columnVo);
            } else if (selectItem instanceof AllTableColumns) {
                // 如果判断是否有“xx.*”， 查询“xx”的所有字段
                AllTableColumns allTableColumns = (AllTableColumns) selectItem;
                String realName = allTableColumns.toString();

                ColumnVo columnVo = new ColumnVo();
                columnVo.setRealName(realName);
                columnVoList.add(columnVo);
            } else {

            }
        }
    }

    private String getTableName(Table fromItem) {
        String name = fromItem.getName();  //表名
        String databaseName = fromItem.getDatabase().getDatabaseName();   // 数据库名
        String schemaName = fromItem.getSchemaName();    //表空间名
        if (databaseName != null && schemaName != null) {
            return databaseName + "." + schemaName + "." + name;
        }
        if (databaseName == null && schemaName != null) {
            return schemaName + "." + name;
        }
        return name;
    }

    @Override
    public void visit(SetOperationList setOperationList) {

    }

    @Override
    public void visit(WithItem withItem) {

    }

    @Override
    public void visit(ValuesStatement valuesStatement) {

    }

    private void processFromItem(PlainSelect pSelect, FromItem fromItem) {
        if (state) {
            // 将查询字段按 字段名：别名 封装到map中
            List<SelectItem> selectColumn = pSelect.getSelectItems();
            for (SelectItem selectItem : selectColumn) {
                if (selectItem instanceof SelectExpressionItem) {
                    SelectExpressionItem expressionItem = (SelectExpressionItem) selectItem;
                    String aliasName = ""; // 字段别名
                    if (expressionItem.getAlias() == null) { // 没有别名
//                        Expression expression = expressionItem.getExpression();
//                        if (expression instanceof Column) {
//                            aliasName = ((Column) expressionItem.getExpression()).getColumnName();
//                        }
                    } else {   // 有别名
                        aliasName = expressionItem.getAlias().getName();
                    }
                    Expression expression = expressionItem.getExpression();
                    analysisFunction(expression, aliasName);
                } else if (selectItem instanceof AllColumns) {
                    String realName = selectItem.toString();
                    putSelectColumn(realName, "", "", "");
                } else if (selectItem instanceof AllTableColumns) {
                    // 如果判断是否有“xx.*”， 查询“xx”的所有字段
                    AllTableColumns allTableColumns = (AllTableColumns) selectItem;
                    String realName = allTableColumns.toString();
                    putSelectColumn(realName, "", "", "");
                } else {
                    String realName = selectItem.toString();
                    putSelectColumn(realName, "", "", "");
//                    // 遇到未知无法解析的sql语句，这里只能后端开发拿到sql语句，测试并完善
//                    throw new ResultException("无法解析当前sql语句，请通知管理员");
                }
            }
        }
        if (!state && fromItem.getAlias() != null) {
            if (!StringUtils.isEmpty(tableAliasName)) {
                tableMappingMap.put(tableAliasName, fromItem.getAlias().getName()); //外层子查询别名：子查询别名
            }
        }
        if (fromItem instanceof Table) {  //表

            String tableName = this.masterTableName = getTableName((Table) fromItem);
            if (fromItem.getAlias() != null) {
                tableAliasName = fromItem.getAlias().getName();
            }
            tableMappingMap.put(tableAliasName, tableName);    // 表别名：表真实名
            tableAliasName = Objects.isNull(tableAliasName) ? tableName : tableAliasName;
        } else if (fromItem instanceof SubSelect) {  // 子查询
            state = false;
            if (fromItem.getAlias() != null) {
                tableAliasName = fromItem.getAlias().getName();
            }
//            tableAliasName = Objects.requireNonNull(fromItem.getAlias()).getName();   //表别名
            ((SubSelect) fromItem).getSelectBody().accept(this);
        }
    }

//    private String getTableName(Table fromItem) {
//        String name = fromItem.getName();  //表名
//        String databaseName = fromItem.getDatabase().getDatabaseName();   //数据库名
//        String schemaName = fromItem.getSchemaName();    //表空间名
//        if (!Objects.isNull(databaseName) && !Objects.isNull(schemaName)) {
//            return databaseName + "." + schemaName + "." + name;
//        }
//        if (Objects.isNull(databaseName) && !Objects.isNull(schemaName)) {
//            return schemaName + "." + name;
//        }
//        return name;
//    }

    private Expression analysisFunction(Expression expression, String aliasName) {
        // 有些字段sql语句带“()”
        if (expression instanceof Parenthesis) {
            expression = ((Parenthesis) expression).getExpression();
        }

        if (judgeExpression(expression)) {
            if (expression instanceof CaseExpression) {
                CaseExpression caseExpression = (CaseExpression) expression;
                Expression switchExpression = caseExpression.getSwitchExpression();
                String nameInTheCase = switchExpression.toString();

                String realName = expression.toString();
                String tempName = "";
                try {
                    tempName = "CASE_" + DigestUtils.md5DigestAsHex(realName.getBytes());
                } catch (Exception e) {
                    e.printStackTrace();
                }

                putSelectColumn(expression, aliasName, tempName, nameInTheCase);
            } else if (expression instanceof Function) {    // 函数
                Function function = (Function) expression;
                List<Expression> expressions = function.getParameters().getExpressions();

                for (Expression ex : expressions) {
                    if (judgeExpression(ex)) {
                        expression = analysisFunction(ex, aliasName);
                    } else {
                        if (judgeValueExpression(ex)) {
                            expression = analysisFunction(ex, aliasName);
                        }
                    }
                }
            } else if (expression instanceof CastExpression) {    // cast函数
                Expression leftExpression = ((CastExpression) expression).getLeftExpression();
                if (judgeExpression(leftExpression)) {
                    expression = leftExpression;
                }
            } else if (expression instanceof BinaryExpression) {   // concat函数
                Expression leftExpression = ((BinaryExpression) expression).getLeftExpression();    //左侧表达式;

                String realName = leftExpression.toString();
                String tempName = "";
                try {
                    tempName = "CONCAT_" + DigestUtils.md5DigestAsHex(realName.getBytes());
                } catch (Exception e) {
                    e.printStackTrace();
                }

                if (judgeExpression(leftExpression)) {
                    expression = leftExpression;
                } else {
                    if (judgeValueExpression(leftExpression)) {
                        putSelectColumn(leftExpression, aliasName, tempName, "");
                    }
                    expression = ((BinaryExpression) expression).getRightExpression();     // 右侧表达式
                }
            }
        } else if (judgeValueExpression(expression)) {
            putSelectColumn(expression, aliasName, "", "");
        }
        return expression;
    }


    //判断查询字段中是否包含函数
    private boolean judgeExpression(Expression expression) {
        if (!Objects.isNull(expression)) {
            return expression instanceof Function || expression instanceof CastExpression || expression instanceof CaseExpression || expression instanceof BinaryExpression;
        } else {
            return false;
        }
    }

    //判断expression是否为值类型
    private boolean judgeValueExpression(Expression expression) {
        return !(expression instanceof StringValue) && !(expression instanceof DoubleValue) && !(expression instanceof LongValue);
    }

    /**
     * @param expression      解析的查询项对象
     * @param aliasName       别名
     * @param tempName        tempName主要针对，使用函数后没有为字段起别名的情况，这里系统会自动起别名，方便后边的动态查询使用
     * @param nameInTheMethod 比如：(CASE s.sex WHEN '1' THEN '男' WHEN '2' THEN '女' ELSE '其他' END) 中nameInTheMethod为 s.sex
     */
    private void putSelectColumn(
            Expression expression,
            String aliasName,
            String tempName,
            String nameInTheMethod
    ) {
        HashMap<String, String> saveData = new HashMap<>();
        if (expression != null) {
            String realName = expression.toString();
            saveData.put("realName", realName);
        }
        saveData.put("alias", aliasName);
        saveData.put("tempName", tempName);
        saveData.put("nameInTheMethod", nameInTheMethod);
        columnList.add(saveData);
    }

    private void putSelectColumn(
            String realName,
            String aliasName,
            String tempName,
            String nameInTheMethod
    ) {
        HashMap<String, String> saveData = new HashMap<>();

        saveData.put("realName", realName);
        saveData.put("alias", aliasName);
        saveData.put("tempName", tempName);
        saveData.put("nameInTheMethod", nameInTheMethod);
        columnList.add(saveData);
    }


    /**
     * 返回字段列表 （有别名就优先返回别名，否则返回全名）
     * @return List<String>
     */
    public List<String> getSqlParseResult() {
        List<String> result = new ArrayList<>();
        for (Map<String, String> map : columnList) {
            String alias = map.get("alias");
            String realName = map.get("realName");
            if (StringUtils.isNotEmpty(alias)) {
                result.add(alias);
            } else {
                result.add(realName);
            }
        }
        return result;
    }

    public List<String> getTableNameSqlParseResult() {
        return new ArrayList<>(tableMappingMap.values());
    }

    public Map<String, String> getTableMappingMap() {
        return tableMappingMap;
    }

    public String getMasterTableName() {
        return masterTableName;
    }

//    private Expression analysisFunction(Expression expression, Map<String, Set<String>> map, String aliasName) {
//        while (judgeExpression(expression)) {
//            while (expression instanceof CaseExpression) {   // case when 函数
//                Expression elseExpression = ((CaseExpression) expression).getElseExpression();   //获取else表达式
//                if (judgeExpression(elseExpression)) {
//                    expression = elseExpression;
//                }
//                else if (!Objects.isNull(elseExpression) && judgeValueExpression(elseExpression)) {
//                    putSelectMap(elseExpression, map, aliasName);
//                }
//
//                for (Expression whenClause : ((CaseExpression) expression).getWhenClauses()) {    //when条件
//                    WhenClause clause = (WhenClause) whenClause;
//                    expression = analysisFunction(clause.getThenExpression(), map, aliasName);   //获取then表达式
//                }
//            }
//            while (expression instanceof Function) {    // 函数
//                Function function = (Function) expression;
//                List<Expression> expressions = function.getParameters().getExpressions();
//                for (Expression ex : expressions) {
//                    if (judgeExpression(ex)) {
//                        expression = analysisFunction(ex, map, aliasName);
//                    } else {
//                        if (judgeValueExpression(ex)) {
//                            expression = analysisFunction(ex, map, aliasName);
//                        }
//                    }
//                }
//            }
//            while (expression instanceof CastExpression) {    // cast函数
//                Expression leftExpression = ((CastExpression) expression).getLeftExpression();
//                if (judgeExpression(leftExpression)) {
//                    expression = leftExpression;
//                }
//            }
//            while (expression instanceof BinaryExpression) {   // concat函数
//                Expression leftExpression = ((BinaryExpression) expression).getLeftExpression();    //左侧表达式;
//                if (judgeExpression(leftExpression)) {
//                    expression = leftExpression;
//                } else {
//                    if (judgeValueExpression(leftExpression)) {
//                        putSelectMap(leftExpression, map, aliasName);
//                    }
//                    expression = ((BinaryExpression) expression).getRightExpression();     // 右侧表达式
//                }
//            }
//        }
//        if (judgeValueExpression(expression)) {
//            putSelectMap(expression, map, aliasName);
//        }
//        return expression;
//    }
    //保存查询字段
//    private void putSelectMap(Expression expression, Map<String, Set<String>> map, String aliasName) {
//        Set<String> set = new HashSet<>();
//        if (!map.containsKey(aliasName)) {
//            set.add(expression.toString());
//        } else {
//            set = map.get(aliasName);
//            set.add(expression.toString());
//        }
//        map.put(aliasName, set);
//    }
}
